﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.auth.dao.impl;

import java.util.*;

import com.hh.bss.auth.dao.*;
import com.hh.bss.auth.entity.*;
import com.hh.bss.auth.service.*;
import com.hh.bss.auth.vo.query.*;
import com.hh.bss.base.*;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class SystemUserDaoImpl extends BaseHibernateDao<SystemUser, java.lang.Integer> implements SystemUserDao<SystemUser, java.lang.Integer> {

	public Class getEntityClass() {
		return SystemUser.class;
	}

	public Page findPage(SystemUserQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { SystemUserQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.account,a.passwd,a.name,a.name_cn nameCn,a.branch_nbr branchNbr,a.business_level businessLevel,a.business_group businessGroup,b.branchCompanyId branchCompanyIdString,b.branchCompanyName,c.deptId,c.deptName,d.titleId titleIdString,d.title,a.title_name titleName,a.status,a.need_checkin needCheckin,a.tel,a.mobile,a.email,'' emailPasswd,a.msn,a.comment,a.hiredate,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,a.title_name_en titleNameEn,group_concat(f.name) roles  from system_user a ");
		// sql.append(" left join system_branch_company b on a.branch_company_id=b.id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.branch_company_id,char)) separator ',') branchCompanyId,group_concat((b5.name) separator ',') branchCompanyName from system_user_title b2,system_branch_title b3,system_branch_dept b4,system_branch_company b5 where  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id=b5.id group by b2.user_id) b on a.id=b.user_id ");
		// sql.append(" left join system_branch_dept c on a.dept_id=c.id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b4.id,char)) separator ',') deptId,group_concat((b4.name) separator ',') deptName from system_user_title b2,system_branch_title b3,system_branch_dept b4 where  b2.title_id=b3.id and b3.dept_id=b4.id  group by b2.user_id) c on a.id=c.user_id ");
		// sql.append(" left join system_branch_title d on a.title_id=d.id ");
		sql.append(" left join (select b2.user_id,group_concat((Convert(b3.id,char)) separator ',') titleId,group_concat((b3.name) separator ',') title from system_user_title b2,system_branch_title b3 where  b2.title_id=b3.id group by b2.user_id) d on a.id=d.user_id ");
		// 查询用户角色
		sql.append(" left join system_user_roles e ON a.id =e.user_id ");
		sql.append(" left join system_role f ON e.role_id =f.id ");
		sql.append(" group by a.id  ");
		sql.append(" ) t where t.account<>'admin' ");

		if (isNotEmpty(query.getName())) {
			sql.append(" and  t.account like ? ");
			values.add("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getStatus())) {
			sql.append(" and  t.status = ? ");
			values.add(query.getStatus());
		}
		if (isNotEmpty(query.getBranchCompanyName())) {
			sql.append(" and  t.branchCompanyName like ? ");
			values.add("%" + query.getBranchCompanyName() + "%");
		}

		if (isNotEmpty(query.getBranchCompanyId())) {
			// sql.append(" and  t.branchCompanyId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3,system_branch_dept b4 where b2.user_id=t.id and  b2.title_id=b3.id and b3.dept_id=b4.id  and  b4.branch_company_id= ? ) ");
			values.add(query.getBranchCompanyId());
		}
		if (isNotEmpty(query.getDeptId())) {
			// sql.append(" and  t.deptId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2,system_branch_title b3 where b2.user_id=t.id and  b2.title_id=b3.id and   b3.dept_id= ? ) ");
			values.add(query.getDeptId());
		}
		if (isNotEmpty(query.getTitleId())) {
			// sql.append(" and  t.titleId = ? ");
			sql.append(" and exists(select 1 from system_user_title b2 where b2.user_id=t.id and b2.title_id= ? ) ");
			values.add(query.getTitleId());
		}
		if (isNotEmpty(query.getTitle())) {
			sql.append(" and  t.title like  ? ");
			values.add("%" + query.getTitle() + "%");
		}

		if (isNotEmpty(query.getSort())) {
			sql.append(" order by t.").append(query.getSort());

			if (isNotEmpty(query.getDir())) {
				sql.append(" ").append(query.getDir());
			}
		}

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public List findUserRole(SystemUserQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select if(c.id is null,'0',c.id) id  ,a.id roleId,a.name,if(c.user_id is null,'0','1') hasRole from  system_role a ");
		sql.append(" left join (select b.id,b.role_id,b.user_id from system_user_roles b where b.user_id=? ) c on c.role_id=a.id ");
		sql.append("  where a.status='N' ");
		sql.append(" ) t where 1=1 ");

		values.add(query.getUserId());

		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
	}

	@Override
	public boolean validateIP(String ip) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select *  from system_ip a where a.ip = ? ");
		sql.append(" ) t where 1=1 ");
		values.add(ip);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			return true;
		}
		return false;
	}

}
